/**
 * Copyright 1995-2006 Finsiel Romania s.r.l. All rights reserved.
 * 
 * This portion of code is intellectual property of Finsiel Romania s.r.l. 
 * You are not allowed to copy, adapt, modify, use this code in any way unless 
 * you have explicit written permission from Finsiel Romania.
 */

package ro.romanescu.utilities.db;

import java.io.BufferedReader;
import java.io.File;

import java.io.FileReader;
import java.io.IOException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * Various database utilities.
 * @author Cristian Romanescu
 * @version 1.0
 */
public final class DatabaseUtilities
{
  
  private static final Log log = LogFactory.getLog( DatabaseUtilities.class );

  /**
   * Close database connection.
   * @param conn Database connection
   */
  public static void closeDatabaseObjects( Connection conn )
  {
    closeDatabaseObjects( conn, null, null );
  }

  /**
   * Close database statement.
   * @param stmt SQL Statement
   */
  public static void closeDatabaseObjects( Statement stmt )
  {
    closeDatabaseObjects( null, stmt, null );
  }

  /**
   * Close database objects.
   * Any of them may be null if not applicable.
   * @param stmt SQL Statement
   * @param rs Result set
   */
  public static void closeDatabaseObjects( Statement stmt, ResultSet rs )
  {
    closeDatabaseObjects( null, stmt, rs );
  }


  /**
   * Close database objects.
   * Any of them may be null if not applicable.
   * @param conn Database connection
   * @param stmt SQL Statement
   * @param rs Result set
   */
  public static void closeDatabaseObjects( Connection conn, Statement stmt, ResultSet rs )
  {
    if( conn != null )
    {
      try
      {
        conn.close();
        conn = null;
      }
      catch( SQLException e )
      {
        e.printStackTrace();
      }

    }
    if( rs != null )
    {
      try
      {
        rs.close();
        rs = null;
      }
      catch( SQLException e )
      {
        e.printStackTrace();
      }
    }
    if( stmt != null )
    {
      try
      {
        stmt.close();
        stmt = null;
      }
      catch( SQLException e )
      {
        e.printStackTrace();
      }
    }
  }

  /**
   * Read text file with an SQL statement on each line (terminated by an
   * cr, lf or cr/lf).
   * <br />
   * Script execution is performed in one transaction. If one statement fails,
   * the whole transaction is rolled back, leaving the database into initial
   * state (before method was called). Connection is not closed and auto-commit
   * mode is left intact.
   * <br />
   * Lines starting with '--' are ignored (considered comments)
   * <br />
   * SQL may or may not end with ';'
   * <br />
   * <strong>WARNING: SQL statement should not span on multiple lines!</strong>
   * @param file File which contains SQL statements
   * @param conn Database connection
   * @return true if all statements were successfully executed
   */
  public static boolean executeSQLFile( File file, Connection conn )
  {
    boolean ret = false;
    PreparedStatement stmt = null;
    Savepoint sp = null;
    String line = null;
    boolean acm = false;
    BufferedReader reader = null;
    int currentLineIdx = 0;
    try
    {
      acm = conn.getAutoCommit();
      conn.setAutoCommit( false );
      reader = new BufferedReader( new FileReader( file ) );
      sp = conn.setSavepoint();
      while( ( line = reader.readLine() ) != null )
      {
        line = line.trim();
        if( line.startsWith( "--" ) )
        {
          continue;
        }
        if( line.equalsIgnoreCase( "" ) )
        {
          continue;
        }
        if( line.endsWith( ";" ) )
        {
          line = line.substring( 0, line.lastIndexOf( ';' ) );
        }
        stmt = conn.prepareStatement( line );
        stmt.execute();
        stmt.close();
      }
      conn.commit();
      ret = true;
    }
    catch( IOException e )
    {
      e.printStackTrace();
    }
    catch( SQLException e )
    {
      System.err.println( "Error on script, line: " + currentLineIdx );
      System.err.println( line );
      if( sp != null )
      {
        try
        {
          conn.rollback( sp );
        }
        catch( SQLException ex )
        {
          ex.printStackTrace();
        }
      }
      e.printStackTrace();
    }
    finally
    {
      try
      {
        reader.close();
      }
      catch( IOException e )
      {
        e.printStackTrace();
      }
      try
      {
        conn.setAutoCommit( acm );
      }
      catch( SQLException e )
      {
        e.printStackTrace();
      }
    }
    return ret;
  }

  /**
   * Execute an SQL statement.
   * @param sql SQL statement
   * @param paramValue
   * @return Number of items resulted from query.
   */
  public static int executeSQLCount( String sql, String paramValue, Connection conn )
  {
    int ret = 0;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    {
      stmt = conn.prepareStatement( sql );
      if( paramValue != null )
      {
        stmt.setString( 1, paramValue );
      }
      log.debug( "Executing JDBC SQL: " + sql );
      rs = stmt.executeQuery();
      if( rs.next() )
      {
        ret = rs.getInt( 1 );
      }
    }
    catch( SQLException e1 )
    {
      e1.printStackTrace();
    }
    finally
    {
      closeDatabaseObjects( stmt, rs );
    }
    return ret;
  }
  
  /**
   * Rollback an transaction to a specified savepoint. If savepoint is null, whole
   * transaction is rolled back.
   * @param conn Connection
   * @param sp Save point
   */
  public static void rollback( Connection conn, Savepoint sp )
  {
    try 
    { 
      if( sp == null )
      {
        conn.rollback();
      }
      else
      {
        conn.rollback( sp );
      }
    }
    catch( SQLException e )
    {
      e.printStackTrace();
    }
  }
  
  /**
   * Rollback whole active transaction.
   * @param conn Connection
   */
  public static void rollback( Connection conn )
  {
    rollback( conn, null );
  }
  
  /**
   * Set auto-commit status on connection.
   * @param conn Connection
   * @param autocommit status
   */
  public static void autocommit( Connection conn, boolean autocommit )
  {
    try
    {
      conn.setAutoCommit( autocommit );
    }
    catch( SQLException e )
    {
      e.printStackTrace();
    }
  }
}
